package com.wx.database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import com.wx.entity.CarNumber;
import com.wx.entity.Office;
import com.wx.entity.User;

public class DatabaseDao {

	// ---------------------------------------------------------------------------------------------
	// 更新

	/**
	 * 更新用户信息
	 * 
	 * @param username
	 * @param tel
	 * @param openid
	 * @return
	 * @throws SQLException
	 */
	public static boolean updateUser(String username, String tel, String openid) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "" + " UPDATE user" + " SET username=?,tel=?" + " WHERE openid=? ";

		// 将sql语句加载到驱动执行程序中，但并不直接执行
		PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);

		ptmt.setString(1, username);
		ptmt.setString(2, tel);
		ptmt.setString(3, openid);

		// 直接sql语句执行
		ptmt.execute();

		return true;
	}

	// ---------------------------------------------------------------------------------------------
	// 删除

	/**
	 * 根据车牌ID删除车牌
	 * 
	 * @param cid
	 * @return
	 */
	public static String deleteCar(Integer cid) {
		Connection conn = DBUtil.getConnection();
		String sql = "" + " DELETE FROM number" + " WHERE cid=?";

		// 将sql语句加载到驱动执行程序中，但并不直接执行
		PreparedStatement ptmt;
		try {
			ptmt = (PreparedStatement) conn.prepareStatement(sql);
			ptmt.setInt(1, cid);
			// 直接sql语句执行
			ptmt.execute();
			return "车牌ID：" + cid + "已成功删除！";
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return "删除失败！请联系系统管理员处理！";
		}
	}

	// ---------------------------------------------------------------------------------------------
	// 新增

	/**
	 * 增加用户
	 * 
	 * @param username
	 * @param tel
	 * @param openid
	 * @throws SQLException
	 */
	public static boolean addUser(String username, String tel, String openid) throws SQLException {
		List<Integer> list = DatabaseDao.openidQueryUid(openid);
		if (list != null && list.size() > 0) {
			return false;
		} else {
			Connection conn = DBUtil.getConnection();
			String sql = " " + " INSERT INTO user" + " (username, tel, openid, pid)" + " VALUES(?,?,?,?)";
			PreparedStatement ptmt;
			try {
				ptmt = (PreparedStatement) conn.prepareStatement(sql);
				ptmt.setString(1, username);
				ptmt.setString(2, tel);
				ptmt.setString(3, openid);
				ptmt.setInt(4, 3);
				ptmt.execute();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return false;
			}
			return true;
		}
	}

	/**
	 * 插入车牌
	 * 
	 * @param uid
	 * @param carnum
	 * @throws SQLException
	 */
	public static String addCar(Integer uid, String carnum) {
		Connection conn = DBUtil.getConnection();
		String sql = "" + " INSERT INTO number" + " (uid, carnum)" + " VALUES(" + "?,?)";
		try {
			PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
			ptmt.setInt(1, uid);
			ptmt.setString(2, carnum);
			ptmt.execute();
			System.out.println("已经插入数据" + uid + " " + carnum);
			return "已成功绑定车牌：" + carnum;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return "绑定车牌失败，请联系系统管理员处理！";
		}
	}

	/**
	 * 插入数据
	 * 
	 * @param openid
	 * @param msgType
	 * @throws SQLException
	 */
	public static void addWork(String openid, String msgType) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "" + " INSERT INTO work" + " (openid, msgType, date, time)" + " VALUES(" + "?,?,?,?)";
		PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);

		SimpleDateFormat sdfDate = new SimpleDateFormat("yyyyMMdd");
		SimpleDateFormat sdfTime = new SimpleDateFormat("HH:mm:ss");

		String dateString = sdfDate.format(new Date());
		String timeString = sdfTime.format(new Date());

		ptmt.setString(1, openid);
		ptmt.setString(2, msgType);
		ptmt.setString(3, dateString);
		ptmt.setString(4, timeString);

		ptmt.execute();
		System.out.println("已经插入数据" + openid + " " + msgType + " " + dateString + " " + timeString);
	}

	// ---------------------------------------------------------------------------------------------
	// 更新

	/**
	 * 更新
	 * 
	 * @param uid
	 * @return
	 * @throws SQLException
	 */
	public static boolean updatePid(int pid, int uid) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "" + " UPDATE user" + " SET pid=? " + " WHERE uid=? ";

		PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
		ptmt.setInt(1, pid);
		ptmt.setInt(2, uid);
		System.out.println(sql);
		if (ptmt.executeUpdate() == 0) {
			return false;
		} else {
			return true;
		}
	}

	// ---------------------------------------------------------------------------------------------
	// 查询

	/**
	 * 查询用户表中所有用户信息
	 * 
	 * @return
	 * @throws SQLException
	 */
	public static List<User> queryUser() throws SQLException {
		List<User> list = new ArrayList<>();
		Connection conn = DBUtil.getConnection();
		Statement stmt = (Statement) conn.createStatement();
		String sql = "" + " SELECT a.uid,a.username,a.tel,b.type" + " FROM user a, property b"
				+ " WHERE a.pid = b.pid;";
		ResultSet rs = stmt.executeQuery(sql);
		User user;
		while (rs.next()) {
			user = new User();
			user.setUid(rs.getInt(1));
			user.setUsername(rs.getString(2));
			user.setTel(rs.getString(3));
			user.setPidStr(rs.getString(4));
			list.add(user);
			user = null;
		}
		return list;
	}

	/**
	 * 根据用户uid查询用户信息
	 * 
	 * @param uid
	 * @return
	 * @throws SQLException
	 */
	public static List<User> uidQueryUser(int uid) throws SQLException {
		List<User> list = new ArrayList<User>();
		Connection conn = DBUtil.getConnection();
		Statement stmt = (Statement) conn.createStatement();
		String sql = "" + " SELECT uid,username,tel,openid,pid,block" + " FROM user " + " WHERE uid=" + uid + ";";
		ResultSet rs = stmt.executeQuery(sql);
		User user;
		while (rs.next()) {
			user = new User();
			user.setUid(rs.getInt(1));
			user.setUsername(rs.getString(2));
			user.setTel(rs.getString(3));
			user.setOpenid(rs.getString(4));
			user.setBlock(rs.getInt(5));
			list.add(user);
			user = null;
		}
		return list;
	}

	/**
	 * 根据用户uid查询其绑定的车牌
	 * 
	 * @param uid
	 * @return
	 * @throws SQLException
	 */
	public static List<CarNumber> uidQueryCarnum(int uid) throws SQLException {
		List<CarNumber> list = new ArrayList<CarNumber>();
		Connection conn = DBUtil.getConnection();
		Statement stmt = (Statement) conn.createStatement();
		String sql = "" + " SELECT cid,carnum" + " FROM number" + " WHERE uid=" + uid + ";";
		ResultSet rs = stmt.executeQuery(sql);
		CarNumber carNumber;
		while (rs.next()) {
			carNumber = new CarNumber();
			carNumber.setCid(rs.getInt(1));
			carNumber.setCarnum(rs.getString(2));
			carNumber.setUid(uid);
			list.add(carNumber);
		}
		return list;
	}

	/**
	 * 根据openid查询用户权限 return 1:最高权限 return 2:普通管理员 return 3:一般用户
	 * 
	 * @param openid
	 * @return
	 * @throws SQLException
	 */
	public static int queryPid(String openid) throws SQLException {
		Connection conn = DBUtil.getConnection();
		Statement stmt = (Statement) conn.createStatement();
		String sql = "" + " SELECT pid" + " FROM user" + " WHERE openid=\"" + openid + "\";";

		System.out.println(sql);

		ResultSet rs = stmt.executeQuery(sql);
		int num = 3;
		while (rs.next()) {
			num = rs.getInt(1);
		}
		return num;
	}

	/**
	 * 根据openid查询用户Uid
	 * 
	 * @param openid
	 * @return
	 * @throws SQLException
	 */
	public static List<Integer> openidQueryUid(String openid) throws SQLException {
		List<Integer> list = new ArrayList<Integer>();
		Connection conn = DBUtil.getConnection();
		Statement stmt = (Statement) conn.createStatement();
		String sql = "" + " SELECT uid" + " FROM user" + " WHERE openid=\"" + openid + "\";";
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			list.add(rs.getInt(1));
		}
		return list;
	}

	/**
	 * 根据电话查询用户Uid
	 * 
	 * @param tel
	 * @return
	 * @throws SQLException
	 */
	public static List<Integer> telQueryUid(String tel) throws SQLException {
		List<Integer> list = new ArrayList<Integer>();
		Connection conn = DBUtil.getConnection();
		Statement stmt = (Statement) conn.createStatement();
		String sql = "" + " SELECT uid" + " FROM user" + " WHERE tel='" + tel + "';";
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			list.add(rs.getInt(1));
		}
		return list;
	}

	/**
	 * 根据用户名查询用户Uid
	 * 
	 * @param username
	 * @return
	 * @throws SQLException
	 */
	public static List<Integer> usernameQueryUid(String username) throws SQLException {
		List<Integer> list = new ArrayList<Integer>();
		Connection conn = DBUtil.getConnection();
		Statement stmt = (Statement) conn.createStatement();
		String sql = "" + " SELECT uid" + " FROM user" + " WHERE username='" + username + "';";
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			list.add(rs.getInt(1));
		}
		return list;
	}

	/**
	 * 根据车牌查询用户Uid
	 * 
	 * @param carnum
	 * @return
	 * @throws SQLException
	 */
	public static List<Integer> carnumQueryUid(String carnum) throws SQLException {
		List<Integer> list = new ArrayList<Integer>();
		Connection conn = DBUtil.getConnection();
		Statement stmt = (Statement) conn.createStatement();
		String sql = "" + " SELECT uid" + " FROM number" + " WHERE carnum='" + carnum + "';";
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			list.add(rs.getInt(1));
		}
		return list;
	}

	/**
	 * 根据用户权限查询用户uid
	 * 
	 * @param pid
	 * @return
	 * @throws SQLException
	 */
	public static List<User> pidQueryUid(int pid) throws SQLException {
		List<User> list = new ArrayList<User>();
		Connection conn = DBUtil.getConnection();
		Statement stmt = (Statement) conn.createStatement();
		String sql = "" + " SELECT uid,username" + " FROM user" + " WHERE pid=" + pid + ";";
		ResultSet rs = stmt.executeQuery(sql);
		User user;
		while (rs.next()) {
			user = new User();
			user.setUid(rs.getInt(1));
			user.setUsername(rs.getString(2));
			list.add(user);
			user = null;
		}
		return list;
	}

	/**
	 * 根据车牌cid查询用户uid
	 * @param cid
	 * @return
	 */
	public static Integer cidQueryUid(Integer cid) {
		Connection conn = DBUtil.getConnection();
		String sql = ""
				+ " SELECT uid"
				+ " FROM number"
				+ " WHERE cid=?;";
		try {
			PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
			ptmt.setInt(1, cid);
			ResultSet rs = ptmt.executeQuery();
			while (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	
	/**
	 * 根据车牌ID查询车牌号码
	 * @param cid
	 * @return
	 */
	public static String cidQueryCarnum(Integer cid) {
		Connection conn = DBUtil.getConnection();
		String sql = ""
				+ " SELECT carnum"
				+ " FROM number"
				+ " WHERE cid=?;";
		try {
			PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
			ptmt.setInt(1, cid);
			ResultSet rs = ptmt.executeQuery();
			while (rs.next()) {
				return rs.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 根据UID查询车牌记录
	 * 
	 * @param uid
	 * @return
	 */
	public static List<Office> queryOffice(Integer uid) {
		List<Office> list = new ArrayList<>();
		Connection conn = DBUtil.getConnection();
		String sql = "" + " SELECT a.username,a.tel,b.time,c.carnum" + " FROM user a,office b,number c"
				+ " WHERE a.uid=c.uid AND b.cid = c.cid AND a.uid=?";
		try {
			PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
			ptmt.setInt(1, uid);
			ResultSet rs = ptmt.executeQuery();
			Office office;
			while (rs.next()) {
				office = new Office();
				office.setUsername(rs.getString(1));
				office.setTel(rs.getString(2));
				office.setDate(rs.getTimestamp(3));
				office.setCarNumber(rs.getString(4));
				list.add(office);
				office = null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	public static List<Office> queryOffice(Integer uid, Integer cid) {
		List<Office> list = new ArrayList<>();
		Connection conn = DBUtil.getConnection();
		String sql = "" + " SELECT a.username,a.tel,b.time,c.carnum" + " FROM user a,office b,number c"
				+ " WHERE a.uid=c.uid AND b.cid = c.cid AND a.uid=? AND c.cid=?";
		try {
			PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
			ptmt.setInt(1, uid);
			ptmt.setInt(1, cid);
			ResultSet rs = ptmt.executeQuery();
			Office office;
			while (rs.next()) {
				office = new Office();
				office.setUsername(rs.getString(1));
				office.setTel(rs.getString(2));
				office.setDate(rs.getTimestamp(3));
				office.setCarNumber(rs.getString(4));
				list.add(office);
				office = null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
}
